package com.yunti.springboot.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

/**
 * @author: gengjiaping
 * @date: 2020/3/4
 */
@Slf4j
public class ExcelUtil {
    public static final String EXCEL_EXTENSION = "xls";
    /**
     * 获取Excel导入文件数据(注：实体类的字段顺序必须跟excel中对应起来)
     * xxk: 设计有缺陷,不同设备读取字段的顺序可能不一样
     * @param file
     * @param clas
     * @param sheetIndex
     * @return
     */
    @Deprecated
    public static List importExcel(MultipartFile file, Class clas, Integer sheetIndex){
        if (null == file) {
            return Collections.emptyList();
        }
        //得到工作空间
        Workbook workbook = null;
        try {
            workbook = getWorkbook(file.getInputStream(), file.getOriginalFilename());
            //得到工作表
            Sheet sheet = getSheet(workbook, sheetIndex);
            //行
            List<Object> result = new ArrayList<>();
            for (Row row : sheet) {
                if(!checkRow(row)){
                   continue;
                }
                Object entity = clas.newInstance();
                Field[] fields = clas.getDeclaredFields();
                //列

                int index = 0;
                int colIndex = 0;
                for(int col=0;col<fields.length;col++){
                    String name = fields[index].getName();
                    if("serialVersionUID".equals(name)){
                        index ++;
                        continue;
                    }
                    Class<?> type = clas.getDeclaredField(name).getType();
                    // 首字母大写
                    String replace = name.substring(0, 1).toUpperCase()
                            + name.substring(1);
                    //获得setter方法
                    Method setMethod = clas.getMethod("set" + replace, type);
                    setVal(type,row.getCell(colIndex),entity,setMethod);
                    index ++;
                    colIndex ++;
                }
                result.add(entity);
            }
            return result;
        } catch (IOException | InstantiationException | IllegalAccessException e) {
            log.error("文件打开异常", e);
        } catch (NoSuchFieldException | NoSuchMethodException e) {
            log.error(e.getMessage());
        }
        return null;
    }


    public static void setVal(Class<?> type,Cell cell,Object entity,Method setMethod){
        try {
            String val = getCellValue(cell);
            if(StringUtils.isEmpty(val)){
                return;
            }
            if(type.isAssignableFrom(String.class)){
                setMethod.invoke(entity,val);
            }else if(type.isAssignableFrom(int.class) || type.isAssignableFrom(Integer.class)){
                setMethod.invoke(entity,Integer.parseInt(val));
            }else if(type.isAssignableFrom(double.class) || type.isAssignableFrom(Double.class)){
                setMethod.invoke(entity,Double.parseDouble(val));
            }else if(type.isAssignableFrom(boolean.class) || type.isAssignableFrom(Boolean.class)){
                setMethod.invoke(entity,Boolean.parseBoolean(val));
            }else if(type.isAssignableFrom(Date.class)){
                setMethod.invoke(entity, cell.getDateCellValue());
            }else if(type.isAssignableFrom(LocalDateTime.class)){
                Date date = cell.getDateCellValue();
                setMethod.invoke(entity,date.toInstant()
                        .atZone( ZoneId.systemDefault() )
                        .toLocalDateTime());
            }else if(type.isAssignableFrom(LocalDate.class)){
                Date date = cell.getDateCellValue();
                setMethod.invoke(entity,date.toInstant()
                        .atZone( ZoneId.systemDefault() )
                        .toLocalDateTime());
            }else if(type.isAssignableFrom(BigDecimal.class)){
                setMethod.invoke(entity,new BigDecimal(val));
            }
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }

    }


    /**
     * 校验sheet中是否要获取这一行的数据
     * @param row
     * @return
     */
    public static Boolean checkRow(Row row){
           if (row.getRowNum() == -1) {
                return false;
            } else if(row.getRowNum() == 0){
                //第一行表头跳过
                return false;
            }
            return true;
    }


    /**
     * 获取一个sheet
     * @param workbook
     * @param index
     * @return
     */
    public static Sheet getSheet(Workbook workbook, int index) {
        Sheet sheet = workbook.getSheetAt(index);
        if (null == sheet) {
            sheet = workbook.createSheet();
        }
        //行高
        sheet.setDefaultRowHeightInPoints(20);
        //列宽
        sheet.setDefaultColumnWidth(20);
        return sheet;
    }


    /**
     * 从输入流中获取excel工作表
     *
     * @param iStream  输入流
     * @param fileName 带 .xls或.xlsx 后缀的文件名
     * @return 文件名为空返回空;
     * 格式不正确抛出异常;
     * 正常返回excel工作空间对象
     */
    public static Workbook getWorkbook(InputStream iStream, String fileName) {
        Workbook workbook = null;
        try {
            if (null == fileName) {
                return null;
            }
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(iStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(iStream);
            } else {
                throw new IOException("The document type don't support");
            }
        } catch (Exception e) {
            log.error("文件转换成Excel出错", e);
        } finally {
            if (iStream != null) {
                try {
                    iStream.close();
                } catch (IOException e) {
                    log.error("读取文件异常", e);
                }
            }
        }

        return workbook;
    }

    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellTypeEnum()) {
            case  NUMERIC:
                Number number = cell.getNumericCellValue();
                String numberStr = String.valueOf(number);
                if (numberStr.endsWith(".0")) {
//                    取整数
                    numberStr = numberStr.replace(".0", "");
                }
                if (numberStr.indexOf('E') >= 0) {
//                    取整数
                    numberStr = new DecimalFormat("#").format(number);
                }
                return numberStr;
            case  STRING:
                return cell.getStringCellValue().trim();
            //公式
            case  FORMULA:
                return "";
            case  BLANK:
                return "";
            case  BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                break;
        }
        return "";
    }


}
